﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class TAIKHOANBLL
    {
        DataService DB = new DataService();

        //get all values
        public List<TAIKHOAN> getALL()
        {
            string sql = "select * from taikhoan";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<TAIKHOAN> lstTv = new List<TAIKHOAN>();
            foreach (DataRow r in table.Rows)
            {
                TAIKHOAN tk = new TAIKHOAN();
                
                tk.mataikhoan = (int)r[0];
                tk.tentaikhoan = (string)r[1];
                tk.password = (string)r[2];
                tk.mathanhvien = (int)r[3];
                tk.mavaitro = (int)r[4];
                tk.matinhtrangtk = (int)r[5];

                lstTv.Add(tk);
            }
            return lstTv;
        }

        //insert value
        public void insert(string tentaikhoan,string password, int mathanhvien
            , int mavaitro, int matinhtrangtk)
        {

            string sql = "insert into taikhoan values (@tentaikhoan,@password, @mathanhvien, @mavaitro, @matinhtrangtk)";
            SqlParameter paraTentk = new SqlParameter("tentaikhoan", tentaikhoan);
            SqlParameter paraPass = new SqlParameter("password", password);
            SqlParameter paraMatv = new SqlParameter("mathanhvien", mathanhvien);
            SqlParameter paraMavt = new SqlParameter("mavaitro", mavaitro);
            SqlParameter paraMatttk = new SqlParameter("matinhtrangtk", matinhtrangtk);

            DB.ExecuteNonQuery(sql,paraTentk, paraPass, paraMatv, paraMavt, paraMatttk);

        }
        public void update(int mataikhoan , string tentaikhoan,string password, int mathanhvien
            , int mavaitro, int matinhtrangtk)
        {
            string sql = "update set (@password, @mathanhvien, @mavaitro, @matinhtrangtk) from taikhoan where mataikhoan = @mataikhoan";
            SqlParameter paraMatk = new SqlParameter("mataikhoan" ,mataikhoan);
            SqlParameter paraTentk = new SqlParameter("tentaikhoan", tentaikhoan);
            SqlParameter paraPass = new SqlParameter("password", password);
            SqlParameter paraMatv = new SqlParameter("mathanhvien", mathanhvien);
            SqlParameter paraMavt = new SqlParameter("mavaitro", mavaitro);
            SqlParameter paraMatttk = new SqlParameter("matinhtrangtk", matinhtrangtk);

            DB.ExecuteNonQuery(sql, paraTentk,paraPass, paraMatv, paraMavt, paraMatttk);
        }
        public void delete(int mataikhoan)
        {
            string sql = "delete from taikhoan where mataikhoan = @mataikhoan";
            SqlParameter paraMatk = new SqlParameter("mataikhoan", mataikhoan);
            DB.ExecuteNonQuery(sql);
        }

        //get count
        public int count()
        {
            string sql = "select count(*) from taikhoan";
            return Int32.Parse(DB.ExecuteScalar(sql));

        }
        public int getCountOfMataikhoanWithID(int mataikhoan)
        {
            string sql = "select count(*) from taikhoan where mataikhoan = " + mataikhoan.ToString();
            return Int32.Parse(DB.ExecuteScalar(sql));

        }

        public TAIKHOAN getTaikhoanWithTenTk(string tentaikhoan)
        {
            //withTen
            string sql = "select * from taikhoan where tentaikhoan = '" + tentaikhoan + "'";

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            if (table.Rows.Count == 0) return null;

            DataRow r = table.Rows[0];
            TAIKHOAN tk = new TAIKHOAN();

            tk.mataikhoan = (int)r[0];
            tk.tentaikhoan = (string)r[1];
            tk.password = (string)r[2];
            tk.mathanhvien = (int)r[3];
            tk.mavaitro = (int)r[4];
            tk.matinhtrangtk = (int)r[5];

            return tk;

        }

       
        public void update_pass(string password, string tentaikhoan)
        {
            string sql = "update taikhoan set password = @password from taikhoan where tentaikhoan = '" + tentaikhoan +"'";
            SqlParameter paraPass = new SqlParameter("password", password);
            DB.ExecuteNonQuery(sql, paraPass);
        }

        public TAIKHOAN laytatca(int mataikhoan)
        {
            string sql = "select * from taikhoan where mataikhoan =" + mataikhoan.ToString();
           

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            if (table.Rows.Count == 0) return null;

            DataRow r = table.Rows[0];
            TAIKHOAN tk = new TAIKHOAN();

            tk.mataikhoan = (int)r[0];
            tk.tentaikhoan = (string)r[1];
            tk.password = (string)r[2];
            tk.mathanhvien = (int)r[3];
            tk.mavaitro = (int)r[4];
            tk.matinhtrangtk = (int)r[5];

            return tk;
        }

        public bool isExistTenTaikhoan(string tentk) {
            string sql = "select * from taikhoan where tentaikhoan = '" + tentk.ToString() + "'";

            DataTable table = DB.ExecuteReader(sql);
            if(table.Rows.Count != 0) return true;
            else return false;
        }

        public List<TAIKHOAN> getListTaikhoanWithIDThanhvien(int maThanhvien) { 
            string sql = "select tk.mataikhoan, tk.tentaikhoan, tk.password, tk.mathanhvien, tk.mavaitro, tk.matinhtrangtk " + 
                        " from taikhoan tk,thanhvien tv where tk.mathanhvien = tv.mathanhvien and tv.mathanhvien = '" + maThanhvien.ToString() + "'";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<TAIKHOAN> lstTv = new List<TAIKHOAN>();
            foreach (DataRow r in table.Rows)
            {
                TAIKHOAN tk = new TAIKHOAN();

                tk.mataikhoan = (int)r[0];
                tk.tentaikhoan = (string)r[1];
                tk.password = (string)r[2];
                tk.mathanhvien = (int)r[3];
                tk.mavaitro = (int)r[4];
                tk.matinhtrangtk = (int)r[5];

                lstTv.Add(tk);
            }
            return lstTv;
        }

        public bool isBlock(int idTaikhoan) {
            string sql = "select tentinhtrangtk from tinhtrangtk tttk, taikhoan tk "
                        + " where tttk.matinhtrangtk = tk.matinhtrangtk and tk.mataikhoan = " + idTaikhoan.ToString()
                        + " and tentinhtrangtk = 'block'";

            DataTable table = DB.ExecuteReader(sql);
            if (table.Rows.Count != 0) return true;
            else return false;
        }
    }
}
